using System;
using System.Data;
using System.Data.SqlClient;

namespace Framework.Library.Components.UserPersonal
{
    public class Portal_PersonalUser_DB
    {
        public static DataTable GetAllCommandInPortal(int UserID,int PortalID, string Command_Lang)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_GetAllCommandInPortal", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@UserID", UserID);
            dbCmd.Parameters.Add("@PortalID", PortalID);
            dbCmd.Parameters.Add("@Command_Lang", Command_Lang);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static DataTable GetAllPortalVisible(int UserID, string Portal_Lang)
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_GetAllPortalVisible", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@UserID", UserID);
            dbCmd.Parameters.Add("@Portal_Lang", Portal_Lang);
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        
        public static DataTable GetAll()
        {
            DataTable retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_GetAll", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            try
            {
                retVal = new DataTable();
                SqlDataAdapter da = new SqlDataAdapter(dbCmd);
                da.Fill(retVal);
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }
        public static void Delete(int _iD)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_Delete", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@ID", _iD);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }
        public static int Insert(Portal_PersonalUser_Info _portal_PersonalUser_Info)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_Insert", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@UserID", _portal_PersonalUser_Info.UserID);
            dbCmd.Parameters.Add("@PortalID", _portal_PersonalUser_Info.PortalID);
            dbCmd.Parameters.Add("@CommandID", _portal_PersonalUser_Info.CommandID);
            dbCmd.Parameters.Add("@Visible", _portal_PersonalUser_Info.Visible);
            dbCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
                return (int)dbCmd.Parameters["@RETURN_VALUE"].Value;
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static void Update(Portal_PersonalUser_Info _portal_PersonalUser_Info)
        {
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_Update", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@ID", _portal_PersonalUser_Info.ID);
            dbCmd.Parameters.Add("@UserID", _portal_PersonalUser_Info.UserID);
            dbCmd.Parameters.Add("@PortalID", _portal_PersonalUser_Info.PortalID);
            dbCmd.Parameters.Add("@CommandID", _portal_PersonalUser_Info.CommandID);
            dbCmd.Parameters.Add("@Visible", _portal_PersonalUser_Info.Visible);
            try
            {
                dbConn.Open();
                dbCmd.ExecuteNonQuery();
            }
            finally
            {
                dbConn.Close();
            }
        }

        public static Portal_PersonalUser_Info GetInfoByUserPortalCommand(int UserID, int PortalID, int CommandID)
        {
            Portal_PersonalUser_Info retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_GetInfoByUserPortalCommand", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@UserID", UserID);
            dbCmd.Parameters.Add("@PortalID", PortalID);
            dbCmd.Parameters.Add("@CommandID", CommandID);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new Portal_PersonalUser_Info();
                    retVal.ID = Convert.ToInt32(dr["ID"]);
                    retVal.UserID = Convert.ToInt32(dr["UserID"]);
                    retVal.PortalID = Convert.ToInt32(dr["PortalID"]);
                    retVal.CommandID = Convert.ToInt32(dr["CommandID"]);
                    retVal.Visible = Convert.ToBoolean(dr["Visible"]);
                }
                if (dr != null) dr.Close();
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

        public static Portal_PersonalUser_Info GetInfo(int _iD)
        {
            Portal_PersonalUser_Info retVal = null;
            SqlConnection dbConn = new SqlConnection(AppEnv.ConnectionString);
            SqlCommand dbCmd = new SqlCommand("Portal_PersonalUser_GetInfo", dbConn);
            dbCmd.CommandType = CommandType.StoredProcedure;
            dbCmd.Parameters.Add("@ID", _iD);
            try
            {
                dbConn.Open();
                SqlDataReader dr = dbCmd.ExecuteReader();
                if (dr.Read())
                {
                    retVal = new Portal_PersonalUser_Info();
                    retVal.ID = Convert.ToInt32(dr["ID"]);
                    retVal.UserID = Convert.ToInt32(dr["UserID"]);
                    retVal.PortalID = Convert.ToInt32(dr["PortalID"]);
                    retVal.CommandID = Convert.ToInt32(dr["CommandID"]);
                    retVal.Visible = Convert.ToBoolean(dr["Visible"]);
                }
                if (dr != null) dr.Close();
            }
            finally
            {
                dbConn.Close();
            }
            return retVal;
        }

    }
}
